﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using System.Web.Configuration;

namespace AppVulneravel.Models
{
    public class Contatos
    {
        public int Id { get; set; }
        public string Nome { get; set; }
        public string Email { get; set; }
        public string Telefone { get; set; }
        public string Celular { get; set; }
        public string TelComercial { get; set; }
        public int IdUsuario { get; set; }

        public Contatos()
        {
        }
        public Contatos(int pId
                      , string pNome
                      , string pEmail
                      , string pTelefone
                      , string pCelular
                      , string pTelComercial
                      , int pIdUsuario
                  )
        {
            Id = pId;
            Nome = pNome;
            Email = pEmail;
            Telefone = pTelefone;
            Celular = pCelular;
            TelComercial = pTelComercial;
            IdUsuario = pIdUsuario;
        }

        public void Carregar(int id)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT ");
            sql.Append("id");
            sql.Append(",nome");
            sql.Append(",email");
            sql.Append(",telefone");
            sql.Append(",celular");
            sql.Append(",telcomercial");
            sql.Append(",id_usuario");
            sql.Append(" FROM contatos");
            sql.Append(" WHERE id = " + id);

            var connString = WebConfigurationManager
                .ConnectionStrings["TCC"].ConnectionString;
            using (var conn = new SqlConnection(connString))
            {
                var command = new SqlCommand(sql.ToString(), conn);
                command.Connection.Open();
                List<Usuario> u = new List<Usuario>();

                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    this.Id = Convert.ToInt32(reader.GetValue(0).ToString());
                    this.Nome = reader.GetValue(1).ToString();
                    this.Email = reader.GetValue(2).ToString();
                    this.Telefone = reader.GetValue(3).ToString();
                    this.Celular = reader.GetValue(4).ToString();
                    this.TelComercial = reader.GetValue(5).ToString();
                    this.IdUsuario = Convert.ToInt32(reader.GetValue(6).ToString());
                }
            }
        }

        public List<Contatos> CarregarLista(int id)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT ");
            sql.Append("id");
            sql.Append(",nome");
            sql.Append(",email");
            sql.Append(",telefone");
            sql.Append(",celular");
            sql.Append(",telcomercial");
            sql.Append(",id_usuario");
            sql.Append(" FROM contatos");
            sql.Append(" WHERE id_usuario = " + id);

            var connString = WebConfigurationManager.ConnectionStrings["TCC"].ConnectionString;
            using (var conn = new SqlConnection(connString))
            {
                var command = new SqlCommand(sql.ToString(), conn);
                command.Connection.Open();
                List<Usuario> u = new List<Usuario>();

                SqlDataReader reader = command.ExecuteReader();
                List<Contatos> lista = new List<Contatos>();
                while (reader.Read())
                {
                    Contatos c = new Contatos();
                    c.Id = Convert.ToInt32(reader.GetValue(0).ToString());
                    c.Nome = reader.GetValue(1).ToString();
                    c.Email = reader.GetValue(2).ToString();
                    c.Telefone = reader.GetValue(3).ToString();
                    c.Celular = reader.GetValue(4).ToString();
                    c.TelComercial = reader.GetValue(5).ToString();
                    c.IdUsuario = Convert.ToInt32(reader.GetValue(6).ToString());
                    lista.Add(c);
                }
                return lista;
            }
        }


        public bool Incluir()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("INSERT INTO contatos (");
            sql.Append("nome");
            sql.Append(",email");
            sql.Append(",telefone");
            sql.Append(",celular");
            sql.Append(",telcomercial");
            sql.Append(",id_usuario");
            sql.Append(")VALUES('");
            sql.Append(Nome + "','");
            sql.Append(Email + "','");
            sql.Append(Telefone + "','");
            sql.Append(Celular + "','");
            sql.Append(TelComercial + "','");
            sql.Append(IdUsuario);
            sql.Append("')");

            var connString = WebConfigurationManager.ConnectionStrings["TCC"].ConnectionString;
            using (var conn = new SqlConnection(connString))
            {
                var command = new SqlCommand(sql.ToString(), conn);
                command.Connection.Open();

                if (command.ExecuteNonQuery() > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        public bool Alterar()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("UPDATE contatos set ");
            sql.Append("nome = '" + Nome);
            sql.Append("',email = '" + Email);
            sql.Append("',telefone = '" + Telefone);
            sql.Append("',celular = '" + Celular);
            sql.Append("',telcomercial = '" + TelComercial);
            sql.Append("' WHERE id = " + Id);

            var connString = WebConfigurationManager.ConnectionStrings["TCC"].ConnectionString;
            using (var conn = new SqlConnection(connString))
            {
                var command = new SqlCommand(sql.ToString(), conn);
                command.Connection.Open();

                if (command.ExecuteNonQuery() > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        public bool Excluir(int Id)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("DELETE FROM contatos ");
            sql.Append(" WHERE ");
            sql.Append(" id = " + Id);

            var connString = WebConfigurationManager.ConnectionStrings["TCC"].ConnectionString;
            using (var conn = new SqlConnection(connString))
            {
                var command = new SqlCommand(sql.ToString(), conn);
                command.Connection.Open();

                if (command.ExecuteNonQuery() > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        public List<Contatos> BuscarPorNome(string q)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT ");
            sql.Append("id");
            sql.Append(",nome");
            sql.Append(",email");
            sql.Append(",telefone");
            sql.Append(",celular");
            sql.Append(",telcomercial");
            sql.Append(",id_usuario");
            sql.Append(" FROM contatos");
            sql.Append(" WHERE  (nome LIKE '%" + q + "%')");

            var connString = WebConfigurationManager.ConnectionStrings["TCC"].ConnectionString;
            using (var conn = new SqlConnection(connString))
            {
                var command = new SqlCommand(sql.ToString(), conn);
                command.Connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                List<Contatos> lista = new List<Contatos>();
                while (reader.Read())
                {
                    Contatos c = new Contatos();
                    c.Id = Convert.ToInt32(reader.GetValue(0).ToString());
                    c.Nome = reader.GetValue(1).ToString();
                    c.Email = reader.GetValue(2).ToString();
                    c.Telefone = reader.GetValue(3).ToString();
                    c.Celular = reader.GetValue(4).ToString();
                    c.TelComercial = reader.GetValue(5).ToString();
                    c.IdUsuario = Convert.ToInt32(reader.GetValue(6).ToString());
                    lista.Add(c);
                }
                return lista;
            }
        }
    }
}